Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALLOCATE

The ALLOCATE command calculates lower-level data from upper-level data by allocating variable data down a hierarchical dimension. Frequently you allocate data for budgeting, forecasting, and profitability analysis.

Syntax

ALLOCATE source [SOURCE conjoint] [BASIS basisname [ACROSS dimname]] - [TARGET targetname [TARGETLOG targetlogname]] - [USING aggmap] [ERRORLOG errorlogfileunit]

Arguments

source

  A variable or formula that provides the values to allocate. When the source object is a formula, you must also specify a variable with the TARGET keyword. When you specify a variable as source and you do not specify a target variable or a basisname variable, then ALLOCATE uses source as the basis and the target.

SOURCE conjoint

  Specifies a conjoint dimension that contains a list of cells the user has changed. The ALLOCATE command uses this list to produce the smallest target status needed to allocate all of the changed source cells.

BASIS basisname

  Specifies a variable, relation, or formula that provides the data on which the allocation is based. That data determines which cells of the target receive allocated values and, in an even or proportional operation, the amount of the source allocated to a target cell.

  When the OPERATOR specified by a RELATION (for allocation) statement in aggmap is a COPY operator (COPY, MIN, MAX, FIRST, LAST), the basis tells the ALLOCATE command which target cells to update. When the OPERATOR specified is EVEN, then ALLOCATE derives the counts that it uses for allocation from the basis. When the OPERATOR specified is the PROPORTIONAL, then ALLOCATE uses the basis data to determine the amount to allocate to each target cell. When the OPERATOR is HCOPY, HFIRST, HLAST, or HEVEN, then ALLOCATE does not use a BASIS object. Instead, it allocates the source data to all of the target cells in the dimension hierarchy that is specified by the relation named in the RELATION statement.

  When you specify the same variable as both the basis and the target, the current values of the target cells determine the allocation. When you do not specify a basis, then the ALLOCATE command uses the source as the basis.

ACROSS dimname

  Specifies a dimension, which can be a named composite, that the ALLOCATE command loops over to discover the cells in a basis. Because a basis can be a formula, you can realize a significant performance advantage by supplying a looping dimension that eliminates the sparsity from the basis loop.

TARGET targetname

  Specifies a variable to hold the allocated values. When the source object is a formula, then you must specify a target. When the source object is a variable and you do not specify a target, then ALLOCATE uses the source variable as the target.

TARGETLOG targetlogname

  Specifies a variable (identically dimensioned to the targetname variable), or a relation that specifies such a variable, to which ALLOCATE assigns a copy of the allocation. For instance, when ALLOCATE assigns the value of `100` to the cell of the `costs` variable that is specified by the `time` and `product` dimension values `Jan01` and `TV`, and the `targetlog` relation specifies the cell of the `costacct` variable that is specified by the same dimension values, then ALLOCATE assigns the value of `100` to the specified `costacct` cell, also.

USING aggmap

  Specifies the name of a previously-defined aggmap to use for the allocation. When you do not include this phrase, the command uses the default allocation specification for the variable as previously specified using the $ALLOCMAP property.

ERRORLOG errorlogfileunit

  Specifies a file unit that ALLOCATE uses for logging allocation deadlocks, errors, or other information. When the allocation does not generate any deadlocks or errors, ALLOCATE sets errorlogname to `NA`. When the allocation produces one or more deadlocks or errors, the events are sent to the specified file. ALLOCATE writes one line in the file for each allocation source that remains unallocated.

  When you do not specify a file unit with ERRORLOG, ALLOCATE sends the information to the standard output device.

Notes

Preserving Original Basis Values

Often the source, basis, and target objects are the same variable and therefore the original values in the cells of the target variable determine the proportions of the allocation. The allocation overwrites those original values in the target cells with the allocated values. To preserve original values in a variable, specify the original variable as the basis object and save the allocated values to a new variable as the target object. Using different basis and target objects makes it possible for you to preview the allocated data. When you then want to store the allocated values in the same variable as the basis, you can perform the allocation again with the same object as the basis and the target. Another example of using different basis and target objects is using an actuals variable as the basis of the allocation and a budget variable as the target.

Using a Formula as a Source or Basis

Source and basis objects can be formulas, which makes it possible for you to make complex computations and have the results be the source or basis object. For example, when you want to see the sales of individual products that would be necessary to produce a thirty percent increase in sales for the next year, you could express the increase in the following formula.

    DEFINE actualsWanted DECIMAL FORMULA <time, product>
    EQ LAG(actuals, 1, time) * 1.3

You would then use ACTUALSWANTED as the source object with the ALLOCATE command. In this example, you would use the ACTUALS variable as the basis.

Tracking Multiple Allocations

When you specify a variable with the TARGETLOG argument, you can store an allocated value in that variable as well as in the `target` variable. This double entry allocation makes it possible for you to track multiple allocations to the same target cell. For example, when you allocate a series of different costs to the same costs centers, then each allocation increases the values in the target cells. You can keep track of the individual allocations by specifying a different targetlogname variable for each allocation.

Logging Allocation Errors

When you specify a file with the ERRORLOG argument, you can record errors that result from locks and `NA` basis values. The log can provide feedback to an application about which source values remain unallocated. You can use the information to modify the allocation, for example by using a hierarchical operator such as HEVEN in a RELATION statement in the aggmap. You can use the ALLOCERRLOGHEADER and ALLOCERRLOGFORMAT options to format the error log. Within an allocation specification, you can specify other aspects of the error log using the ERRORLOG and ERRORMASK statements.

Logging the Progress of an Allocation

When you specify a file with the POUTFILEUNIT option, then you can record and monitor the progress of an allocation. You can use the file to get feedback during the course of a lengthy allocation and to gain information that might be useful for optimizing the allocation in the future.

Examples

Example 7-16 Direct Even Allocation

This example allocates a value specified at one level of the `time` dimension hierarchy directly to the variable target cells that are specified by lower level values in the hierarchy without allocating values to an intermediate level. The `timemonthyear` relation specifies the hierarchical relationship of the `time` values. The source, basis, and target of the allocation are all the same variable, PROJBUDGET, which is dimensioned by `division`, `time`, and `line`. The `time` dimension is a nonunique concat dimension that has as its base dimensions `year`, `quarter`, and `month`. The `time` dimension is limited to `<year: Yr02>`, `<quarter: Q1.02>`, `<quarter: q1.02>`, and `<month: Jan02>` to `<month: Jun02>`. The following statements define the `projbudget` variable, set the value of a cell in to `6000` and then report the variable.

    DEFINE projbudget VARIABLE DECIMAL <division time line>
    projbudget(division 'CAMPING' time '<YEAR: YR02>' line  'MARKETING') = 6000
    REPORT projbudget

The preceding statement produces the following results.

    LINE: MARKETING
                     -PROJBUDGET--
                     --DIVISION---
    TIME                CAMPING
    ---------------- -------------
    <year: Yr02>          6,000.00
    <quarter: Q1.02>            NA
    <quarter: Q2.02>            NA
    <month: Jan02>              NA
    <month: Feb02>              NA
    <month: Mar02>              NA
    <month: Apr02>              NA
    <month: May02>              NA
    <month: Jun02>              NA

The following statements define a self-relation on the `time` dimension, relate the `month` values directly to the `year` values, and report the values of the relation.

    DEFINE timemonthyear RELATION time <time>
    LIMIT month TO 'JAN02' TO 'JUN02'
    timemonthyear(time month) = '<YEAR: YR02>'
    REPORT timemonthyear

The preceding statement produces the following results.

    TIME             TIMEMONTHYEAR
    ---------------- -------------
    <year: Yr02>     NA
    <quarter: Q1.02> NA
    <quarter: Q2.02> NA
    <month: Jan02>   <year: Yr02>
    <month: Feb02>   <year: Yr02>
    <month: Mar02>   <year: Yr02>
    <month: Apr02>   <year: Yr02>
    <month: May02>   <year: Yr02>
    <month: Jun02>   <year: Yr02>

The following statements define an aggmap and enter statements into the allocation specification. They allocate the value that is specified by `<year: Yr02>` from `projbudget` to the cells of the same variable that are specified by the `month` dimension values, and then report `projbudget`. The target cells of the variable have `NA` values so the RELATION statement in the allocation specification specifies the HEVEN operator. The ALLOCATE command specifies only one variable, `projbudget`, so that variable is the source and target of the allocation. No basis object is required because the allocation is an HEVEN operation. The allocation is directly from the `year` source value to the `month` target values because that is the hierarchy specified by the relation in the allocation specification.

    DEFINE projbudgmap AGGMAP
    ALLOCMAP
    RELATION timemonthyear OPERATOR HEVEN
    END
    ALLOCATE projbudget USING projbudgmap
    REPORT projbudget

The preceding statement produces the following results.

    LINE: MARKETING
                     -PROJBUDGET--
                     --DIVISION---
    TIME                CAMPING
    ---------------- -------------
    <YEAR: YR02>          6,000.00
    <QUARTER: Q1.02>            NA
    <QUARTER: Q2.02>            NA
    <MONTH: JAN02>        1,000.00
    ...
    <MONTH: JUN02>        1,000.00

Example 7-17 Recursive Even Allocation with a Lock

This example allocates a value specified at one level of the `time` dimension hierarchy first to the target cells at an intermediate level in a variable and then to the cells that are specified by the lowest level values in the hierarchy. The `timeparent` relation specifies the hierarchical relationship of the `time` values. The source, basis, and target of the allocation are `projbudget`. The status of the `division`, `time`, and `line` dimensions are the same as the direct allocation example. At the beginning of this example, the `projbudget` variable again has just the single value, `6000`, in the cell specified by `<year: Yr02>`.

    DEFINE timeparent RELATION time <time>
    LIMIT quarter TO 'Q1.02' 'Q2.02'
    timeparent(time quarter) = '<YEAR: YR02>'
    LIMIT month TO 'JAN02' TO 'MAR02'
    timeparent(time month) = '<QUARTER: Q1.02>'
    LIMIT month TO 'APR02' TO 'JUN02'
    timeparent(time month) = '<QUARTER: Q1.02>'
    REPORT timeparent

The preceding statement produces the following results.

    TIME             TIMEPARENT
    ---------------- -------------
    <year: Yr02>     NA
    <quarter: Q1.02> <year: Yr02>
    <quarter: Q2.02> <year: Yr02>
    <month: Jan02>   <quarter: Q1.02>
    <month: Feb02>   <quarter: Q1.02>
    <month: Mar02>   <quarter: Q1.02>
    <month: Apr02>   <quarter: Q2.02>
    <month: May02>   <quarter: Q2.02>
    <month: Jun02>   <quarter: Q2.02>

This example demonstrates locking a cell so that it does not participate in the allocation. Locking a cell requires a valueset, so the following statements define one, limit the `time` dimension to the desired value, assign a value to the valueset, and then reset the status of the `time` dimension.

    DEFINE timeval TO '<QUARTER: Q2.02>'
    LIMIT time TO '<Year: YR02>' '<Quarter: Q1.02>'  '<Quarter: Q2.02>' -
       '<month: Jan02>' '<month: Feb02>' '<month: Mar02>' -
       '<month: Apr02>' '<month: May02>' '<month: Jun02>

The following statements revise the specification of the aggmap named `projbudgmap`. This time the RELATION statement in the allocation specification specifies the `timeparent` relation, the HEVEN operator, and the PROTECT argument. The READWRITE keyword specifies that the children of the locked cell also do not participate in the allocation. The NONORMALIZE keyword specifies that the value of the locked cell is not subtracted from the source value before it is allocated to the target cells. The statements then allocate the source value and report the results.

    CONSIDER projbudgmap
    ALLOCMAP
    RELATION timeparent OPERATOR HEVEN ARGS PROTECT NONORMALIZE READWRITE timeval
    END

    ALLOCATE projbudget USING projbudgmap
    REPORT projbudget

The preceding statement produces the following results.

    LINE: MARKETING
                     -PROJBUDGET--
                     --DIVISION---
    TIME                CAMPING
    ---------------- -------------
    <year: Yr02>          6,000.00
    <quarter: Q1.02>      6,000.00
    <quarter: Q2.02>            NA
    <month: Jan02>        2,000.00
    <month: Feb02>        2,000.00
    <month: Mar02>        2,000.00
    <month: Apr02>              NA
    <month: May02>              NA
    <month: Jun02>              NA

Example 7-18 Recursive Proportional Allocation

This example uses the same relation as the recursive even allocation but it uses the PROPORTIONAL operator and it does not lock any cells. Because a proportional allocation uses the values of the basis object to calculate the values to assign to the target cells, the `projbudget` variable has values assigned to each of its cells. The value of the `<year: Yr02>` cell is `6000`., which was assigned to that cell. It is not the value an aggregation of the lower levels. A report of `projbudget` before the allocation produces the following results.

    LINE: MARKETING
                     -PROJBUDGET--
                     --DIVISION---
    TIME                CAMPING
    ---------------- -------------
    <year: Yr02>          6,000.00
    <quarter: Q1.02>      1,000.00
    <quarter: Q2.02>      2,000.00
    <month: Jan02>          300.00
    <month: Feb02>          100.00
    <month: Mar02>          600.00
    <month: Apr02>          400.00
    <month: May02>          800.00
    <month: Jun02>          800.00

The following statements replace the previous specification of the aggmap with the new RELATION statement, which specifies the PROPORTIONAL operator. The allocation specification includes a `SOURCEVAL` `ZERO` statement, which specifies that the source value is replace with a zero value after the allocation (see SOURCEVAL for more information). The statements then allocate the source value and report the result.

    CONSIDER projbudgmap
    ALLOCMAP JOINLINES('RELATION timeparent OPERATOR PROPORTIONAL timeval' -
      'SOURCEVAL ZERO' -
      'END')
    ALLOCATE projbudget USING projbudgmap
    REPORT projbudget

The preceding statement produces the following results.

    TIME             TIMEPARENT
    LINE: MARKETING
                     -PROJBUDGET--
                     --DIVISION---
    TIME                CAMPING
    ---------------- -------------
    <year: Yr02>                 0
    <quarter: Q1.02>      2,000.00
    <quarter: Q2.02>      4,000.00
    <month: Jan02>          600.00
    <month: Feb02>          200.00
    <month: Mar02>        1,200.00
    <month: Apr02>          800.00
    <month: May02>        1,600.00
    <month: Jun02>        1,600.00